package com.lz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.lz.form.ItemCategoryDTO;
import com.lz.util.db.SequenceGenUtil;
public class CategoryDAO{

public void create(Connection conn,ItemCategoryDTO dto){
String sql="insert into category(categoryid,categoryname)  values(?,?)";
PreparedStatement pstmt=null;
try{

 pstmt=conn.prepareStatement(sql);

String genId=SequenceGenUtil.getNextId(conn,"bookcategory");
pstmt.setString(1,genId);
dto.setCategoryid(genId);
pstmt.setString(2,dto.getCategoryname());

int status=pstmt.executeUpdate();
pstmt.close();
}
catch(Exception e){
e.printStackTrace();
}
finally{
pstmt=null;
}

}

public static void update(Connection conn,ItemCategoryDTO dto){
String sql="update category set categoryname=? where categoryid=?";
PreparedStatement pstmt=null;
try{

 pstmt=conn.prepareStatement(sql);

pstmt.setString(2,dto.getCategoryid());
pstmt.setString(1,dto.getCategoryname());

int status=pstmt.executeUpdate();
pstmt.close();
}
catch(Exception e){
e.printStackTrace();
}
finally{
pstmt=null;
}

}

public static void delete(Connection conn,ItemCategoryDTO dto){
String sql="delete from category where categoryid=?";
PreparedStatement pstmt=null;
try{

pstmt=conn.prepareStatement(sql);

pstmt.setString(1,dto.getCategoryid());

int status=pstmt.executeUpdate();
pstmt.close();
}
catch(Exception e){
e.printStackTrace();
}
finally{
pstmt=null;
}

}
//The input DTO object needs to contain only the primary keys. Other values will be populated by the DAO

public static ItemCategoryDTO populate(Connection conn, ItemCategoryDTO dto){
String sql="select * from  category where categoryid=?";
PreparedStatement pstmt=null;
ResultSet rs=null;
try{

pstmt=conn.prepareStatement(sql);

pstmt.setString(1,dto.getCategoryid());

 rs=pstmt.executeQuery();
while(rs.next()){

dto.setCategoryid(rs.getString("categoryid"));
dto.setCategoryname(rs.getString("categoryname"));

}//eof rs.next

rs.close();
pstmt.close();
}
catch(Exception e){
e.printStackTrace();
}
finally{
rs=null;
pstmt=null;
}
return dto;

}

public static List getAll(Connection conn){
List list=new ArrayList();
String sql="select * from  category";
PreparedStatement pstmt=null;
ResultSet rs=null;
try{

 pstmt=conn.prepareStatement(sql);


 rs=pstmt.executeQuery();
while(rs.next()){
ItemCategoryDTO dto=new ItemCategoryDTO();
dto.setCategoryid(rs.getString("categoryid"));
dto.setCategoryname(rs.getString("categoryname"));list.add(dto);

}//eof rs.next

rs.close();
pstmt.close();
}
catch(Exception e){
e.printStackTrace();
}
finally{
rs=null;
pstmt=null;
}
return list;

}

}//eof class